﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_can_access_by_position]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [dbo].[fn_can_access_by_position];
GO
CREATE FUNCTION [dbo].[fn_can_access_by_position]
(
    @Class_ID   int,    -- the id of the class to validate (UDS_Class.ClassID)
    @UserID     int,    -- the name of the user
    @Act_ID     int     -- the id of the permission (UDS_Proc.Proc_ID)
)
RETURNS BIT
AS
BEGIN

--得到所有父组中拥有此权利的职位ID
DECLARE @pAssingPosition TABLE (Positionid int);
INSERT INTO @pAssingPosition 
    SELECT Position_ID 
        FROM 
            dbo.UDS_Assign_Rule A
            INNER JOIN dbo.UDS_Class C ON A.Team_ID = C.ClassID
        WHERE
            A.Based_ON = 1 /* position */
            AND A.Act_ID = @Act_id
            AND C.ClassID IN (
                SELECT ClassID FROM dbo.fn_get_parent_class(@Class_ID)
            );

--返回用户是否在有权利的职位ID中
IF EXISTS (
    SELECT 1
    FROM
        dbo.UDS_Staff_In_Position SIP
        INNER JOIN @pAssingPosition AP ON SIP.Position_ID = AP.PositionID 
    WHERE
        SIP.staff_id = @UserID
)
BEGIN
    RETURN 1;
END

-- Access denied.
RETURN 0;
END
GO